package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Optional;

public class LoginModel {

	public static boolean isLoginNameAvailable(String username) throws  DatabaseException{
		String query = "select name from Users where login_name = ?" ;
		boolean available = true;
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection();PreparedStatement preparedStatement = con.prepareStatement(query);){
			preparedStatement.setString(1, username);
			rs = preparedStatement.executeQuery();
			if(rs != null && rs.next()){
				available = false;
			}
		}catch (Exception e){
			throw new DatabaseException(e);
		}finally{
			if(rs!=null){ 
				try {
					rs.close();
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
		return available;
	}
	
	public static boolean isThereAdminAccount() throws  DatabaseException{
		String query = "select name from Users where account_type='A'" ;
		boolean adminAccountExists = false;
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection();Statement statement = con.createStatement();){
			rs = statement.executeQuery(query);
			if(rs != null && rs.next()){
				adminAccountExists = true;
			}
		}catch (Exception e){
			throw new DatabaseException(e);
		}finally{
			if(rs!=null){ 
				try {
					rs.close();
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
		return adminAccountExists;
	}
	
	public static Optional<User> getUser(String username, String password) throws  DatabaseException{
		String query = "select u.id, u.name, u.email, u.account_type, u.gender, u.bulletin,"+
				"u.phone,l.ADDRESS_LINE1, l.ADDRESS_LINE2, l.POSTAL_CODE,"+
				"c.NAME as city, r.name as region, c.name as country, l.id as location_id "+
				"from USERS u left outer join ENTITY_LOCATIONS el on (u.id = el.entity_id) "+
				"left outer join LOCATIONS l on (el.location_id = l.id) "+
				"left outer join CITIES c on (l.city_id = c.id) "+
				"left outer join REGIONS r on (c.region_id = r.id) "+
				"left outer join COUNTRIES c on (r.country_id = c.id) "+
				"where u.login_name = ? and u.password = ? ";
		User user = null;
		Address address = null;
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection();PreparedStatement preparedStatement = con.prepareStatement(query);){
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, password);
			rs = preparedStatement.executeQuery();
			while(rs != null && rs.next()){
				if(user==null){
					user = new User(rs.getLong("id"), username, password, rs.getString("name"), rs.getString("email"), rs.getString("phone"), rs.getString("gender"), rs.getString("bulletin"), rs.getString("account_type"));
				}
				address = new Address(rs.getLong("location_id"), rs.getString("country"), rs.getString("region"), rs.getString("city"), rs.getString("address_line1"), rs.getString("address_line2"), rs.getString("postal_code"));
				user.addAddress(address);
			}
		}catch (Exception e){
			throw new DatabaseException(e);
		}finally{
			if(rs!=null){ 
				try {
					rs.close();
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
		return Optional.ofNullable(user);
	}
	

	public static Optional<User> getUser(long userId) throws  DatabaseException{
		String query = "select u.id, u.login_name, u.password, u.name, u.email, u.account_type, u.gender, u.bulletin,"+
				"u.phone,l.ADDRESS_LINE1, l.ADDRESS_LINE2, l.POSTAL_CODE,"+
				"c.NAME as city, r.name as region, c.name as country, l.id as location_id "+
				"from USERS u left outer join ENTITY_LOCATIONS el on (u.id = el.entity_id) "+
				"left outer join LOCATIONS l on (el.location_id = l.id) "+
				"left outer join CITIES c on (l.city_id = c.id) "+
				"left outer join REGIONS r on (c.region_id = r.id) "+
				"left outer join COUNTRIES c on (r.country_id = c.id) "+
				"where u.id = ? ";
		User user = null;
		Address address = null;
		ResultSet rs = null;
		try(Connection con = ConnectionPool.getConnection();PreparedStatement preparedStatement = con.prepareStatement(query);){
			preparedStatement.setLong(1, userId);
			rs = preparedStatement.executeQuery();
			while(rs != null && rs.next()){
				if(user==null){
					user = new User(rs.getLong("id"), rs.getString("login_name"), rs.getString("password"), rs.getString("name"), rs.getString("email"), rs.getString("phone"), rs.getString("gender"), rs.getString("bulletin"), rs.getString("account_type"));
				}
				address = new Address(rs.getLong("location_id"), rs.getString("country"), rs.getString("region"), rs.getString("city"), rs.getString("address_line1"), rs.getString("address_line2"), rs.getString("postal_code"));
				user.addAddress(address);
			}
		}catch (Exception e){
			throw new DatabaseException(e);
		}finally{
			if(rs!=null){ 
				try {
					rs.close();
				} catch (SQLException e) {
					throw new DatabaseException(e);
				}
			}
		}
		return Optional.ofNullable(user);
	}
	
//	public static void main(String[] args) throws DatabaseException {
//		boolean available = isLoginNameAvailable("nike11");
//		System.out.println(available);
//		Optional<User> user = getUser("gosho", "losho");
//		if (user.isPresent()){
//			User actualUser = user.get();
//			System.out.println(actualUser);
//		}else{
//			System.out.println("No such user!");
//		}
//		System.out.println(isAdminAccount());
//	}
}
